package application.LoginAndAdministrationModule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import application.CommunicationModule.Communication;


public class Registration
{
    PreparedStatement pstmt;
    Szyfr koder;
	
	//Baza
	Communication connector;
	String query;
	ResultSet result;
	Connection conn;
	String url;
	String username;
	String pass;
	//
    
	public Registration()
	{
		conn = null;
		pstmt = null;
		
		koder = new Szyfr();
		 username = "inzynier_admin";
		    pass = "admin";
		    try {
				connector = new Communication(username, pass);
				conn = connector.getConnection();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	 
	//metoda musi dosta� w parametrze otwarte po��czenie
	//zwraca pierwsze wolne id w bazie adres�w - Addresses
	private Integer getAvailibleIdFromAddresses(Connection conn) throws Exception
	{
		Integer x = 1;
		boolean found = false;
		ArrayList<Integer> myResult = new ArrayList<Integer>();
		query = "SELECT id FROM \"Addresses\"";
		pstmt = conn.prepareStatement(query); //(String sql, int resultSetType, int resultSetConcurrency)
		result = pstmt.executeQuery();
		
		try
		{
			while(result.next())
			{
				myResult.add(result.getInt(1));
			}
			
			for(int i=0;i<myResult.size();i++)
			{
				for(int j=0;j<myResult.size();j++)
				{
					if(myResult.get(j)==x)
					{
						found = true;
						x++;
						break;
					}
					found=false;
				}
				if(found==false) return x;
			}
			if(found==true) return x;
		}
		catch (Exception e)
		{
			return -1;
		}
		
		pstmt.close();
		return 1;
	}
	
	//metoda musi dosta� w parametrze otwarte po��czenie
	//zwraca pierwsze wolne id w bazie u�ytkownik�w - Users
	private Integer getAvailibleIdFromUsers(Connection conn) throws Exception
	{
		Integer x = 1;
		boolean found = false;
		ArrayList<Integer> myResult = new ArrayList<Integer>();
		query = "SELECT id_user FROM \"Users\"";
		pstmt = conn.prepareStatement(query); //(String sql, int resultSetType, int resultSetConcurrency)
		result = pstmt.executeQuery();
		
		try
		{
			while(result.next())
			{
				myResult.add(result.getInt(1));
			}
			
			for(int i=0;i<myResult.size();i++)
			{
				for(int j=0;j<myResult.size();j++)
				{
					if(myResult.get(j)==x)
					{
						found = true;
						x++;
						break;
					}
					found=false;
				}
				if(found==false) return x;
			}
			if(found==true) return x;
		}
		catch (Exception e)
		{
			return -1;
		}
		
		pstmt.close();
		return 1;
	}
	//metoda sprawdza czy podano wszystkie wymagane pola. S� to:
	//name,surname,login,password
	private boolean validateRequiredFields(String name, String surname, String login, String password)
	{
		if( ( name==null || name.isEmpty() ) || ( surname==null || surname.isEmpty() ) || ( login==null || login.isEmpty() ) || ( password==null || password.isEmpty() )) //sprawdza czy wprowadzono wymagane pola name, surname, login, password
		{
			return false;
		}
		return true;
	}
	
	private boolean validateRightsField(byte right)
	{
		if(right==0 || right==1 || right==2) return true;
		return false;	
	}
	
	//metoda sprawdza czy nazwa u�ytkownika jak� chcemy doda� nie jest ju� zaj�ta
	private boolean checkExistanceOfLogin(String login, Connection conn) throws SQLException
	{
		try
		{
		    query = "SELECT login FROM \"Users\"";
		    pstmt = conn.prepareStatement(query); // tworzy zapytanie
		    result = pstmt.executeQuery(); // wykonuje zapytanie i wynik zwraca do obiektu reult
		    
			while(result.next()) //sprawdza czy u�ytkownik o podanym loginie istnieje w bazie
			{
				if(login.equals(result.getString(1)))
				{
					return true;
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		
		return false;
	}
	
	//metoda dodaje nowy rekord do tabeli Addresses
	private boolean addUserAddress(String street, Integer houseNumber, Integer flatNumber, String city, String postCode, Connection conn) throws Exception
	{
		try
		{
			Integer x = getAvailibleIdFromAddresses(conn);
			query = "INSERT INTO \"Addresses\"(id, street,\"houseNumber\",\"flatNumber\",city,\"postCode\") VALUES (?,?,?,?,?,?)";
		    pstmt = conn.prepareStatement(query);
		    pstmt.setInt(1, x);
		    pstmt.setString(2, street);
		    pstmt.setInt(3, houseNumber);
		    pstmt.setInt(4, flatNumber);
		    pstmt.setString(5, city);
		    pstmt.setString(6, postCode);
		    pstmt.executeUpdate();
		    return true;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		
		return false;
	}
	
	
	private boolean addUser(String name, String surname, String login, String password, byte right, Integer address, String eMail, String phoneNumber) throws SQLException
	{
		
		try
		{
			Integer x = getAvailibleIdFromUsers(conn);
			query = "INSERT INTO \"Users\"(id_user,name,surname,login,password,rights,address,\"eMail\",\"phoneNumber\") VALUES (?,?,?,?,?,?,?,?,?)";
		    pstmt = conn.prepareStatement(query);
		    pstmt.setInt(1, x);
		    pstmt.setString(2, name);
		    pstmt.setString(3, surname);
		    pstmt.setString(4, login);
		    pstmt.setString(5, koder.encrypt(password));
		    pstmt.setByte(6, right);
		    pstmt.setInt(7, address);
		    pstmt.setString(8, eMail);
		    pstmt.setString(9, phoneNumber);
		    pstmt.executeUpdate();
		    return true;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		
		return false;
	}
	
	//metoda usuwa u�ytkownika z tabeli users i zwraca id pozycji w tabeli Addresses, kt�r� nale�y usun��	
	private Integer deleteUser(Connection conn, String login, String password) throws SQLException
	{
		Integer id;

		try
		{
		    query = "select address from \"Users\" where login = ?";
		    pstmt = conn.prepareStatement(query);
		    pstmt.setString(1, login);
		    result = pstmt.executeQuery();
		    result.next();
		    id = result.getInt(1);
		    
		    
			query = "DELETE FROM \"Users\" where login = ? and password = ?";
			pstmt = conn.prepareStatement(query);
		    pstmt.setString(1, login);
		    pstmt.setString(2, koder.encrypt(password));
		    pstmt.executeUpdate();
		    
		    return id;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		    
		return -1;
	}
	
	
	private boolean deleteAddress(Connection conn, Integer id) throws SQLException
	{
		
		try
		{	    
			query = "DELETE FROM \"Addresses\" where id=?";
			pstmt = conn.prepareStatement(query);
		    pstmt.setInt(1, id);
		    pstmt.executeUpdate();
		    
		    return true;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		    
		return false;
	}
	
	private boolean deletePositionInAddressesWhileErrorOccour(Connection conn, Integer idToDelete) throws SQLException
	{
		try
		{
			query = "DELETE FROM \"Addresses\" WHERE id=?";
		    pstmt = conn.prepareStatement(query);
		    pstmt.setInt(1,idToDelete);
		    pstmt.execute();
		    return true;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			pstmt.close();
		}
		
		return false;
	}
	public boolean register(String name, String surname, String login, String password, byte right, String eMail, String phoneNumber, String street, Integer houseNumber, Integer flatNumber, String city, String postCode) throws Exception
	{
		Integer addressReference = getAvailibleIdFromAddresses(conn); //zawiera id pod jaki zostanie wprowadzony adres

		if(!validateRequiredFields(name,surname,login,password)) return false;
		if(!validateRightsField(right)) return false;
		if(checkExistanceOfLogin(login,conn)) return false;

		if(addUserAddress(street,houseNumber,flatNumber,city,postCode,conn)==false)
			System.out.println("An Error occured while adding data to table of addresses");
		if(addUser(name,surname,login,password,right,addressReference,eMail,phoneNumber)==false)
		{
			deletePositionInAddressesWhileErrorOccour(conn,addressReference);
		}
		
	    return true;
	}
	
	public boolean unregister(String login, String password) throws Exception 
	{
		Integer id;
		
		id = deleteUser(conn,login,password);
		deleteAddress(conn,id);
		
		conn.close();
		return true;
	}
}
